-- 1.创建项目成本核算结果明细分区表自增序列
declare v_max_id int;
        -- 定义sql语句
        v_sql nvarchar(5000);
begin
	-- 删除序列
	set v_sql = 'drop sequence if exists cost.seq_pro_cost_cal_res_detail';
    execute immediate v_sql;

    -- 创建序列
    set v_sql = 'create sequence cost.seq_pro_cost_cal_res_detail increment by 1 maxvalue 999999999999 minvalue 1';
	execute immediate v_sql;

	select nvl(max(id) + 1, 1) into v_max_id from cost.pro_cost_cal_res_detail where 1 = 1;

    -- 更改步长(increment)为一个你想让序列增加到的值
    set v_sql = concat('alter sequence cost.seq_pro_cost_cal_res_detail increment by ', v_max_id);
    execute immediate v_sql;

    -- 执行一下查询序列的语句
    set v_sql = 'select cost.seq_pro_cost_cal_res_detail.nextval from dual';
    execute immediate v_sql;

    -- 修改为原来的步长
    set v_sql = 'alter sequence cost.seq_pro_cost_cal_res_detail increment by 1';
    execute immediate v_sql;
end;

-- 2.项目成本核算结果明细表重命名
alter table pro_cost_cal_res_detail rename to pro_cost_cal_res_detail_old;

-- 3.创建项目成本核算结果明细分区表
create table cost.pro_cost_cal_res_detail(
    id int default cost.seq_pro_cost_cal_res_detail.nextval not null,
    cal_res_id int null,
    check_scheme_id int null,
    organization_id int null,
    hospital_structure_id int null,
    organization_cal_method int null,
    serve_item_code nvarchar(600) null,
    number decimal(18,4) null,
    price decimal(18,4) null,
    cost_item_code nvarchar(600) null,
    direct_cost decimal(28,12) null,
    indirect_cost_bk decimal(28,12) null,
    indirect_cost_gl decimal(28,12) null,
    indirect_cost_yf decimal(28,12) null,
    medical_cost_type int null,
    create_user_code nvarchar(360) null,
    create_time timestamp null,
    update_user_code nvarchar(360) null,
    update_time timestamp null,
    delete_flag smallint null,
    sink_cost decimal(28,12) null,
    cooperation_cost decimal(28,12) null,
    transfer_sign int null,
    transfer_organization_id int null,
    sink_hr decimal(28,12) null,
    sink_dev decimal(28,12) null,
    cost_sum decimal(28,12) null
) PARTITION BY LIST (check_scheme_id)
  (PARTITION p_crd_1 VALUES(1) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_2 VALUES(2) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_3 VALUES(3) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_4 VALUES(4) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_5 VALUES(5) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_6 VALUES(6) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_7 VALUES(7) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_8 VALUES(8) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_9 VALUES(9) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_10 VALUES(10) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_11 VALUES(11) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_12 VALUES(12) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_13 VALUES(13) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_14 VALUES(14) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_15 VALUES(15) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_16 VALUES(16) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_17 VALUES(17) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_18 VALUES(18) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_19 VALUES(19) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_20 VALUES(20) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_21 VALUES(21) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_22 VALUES(22) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_23 VALUES(23) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_24 VALUES(24) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_25 VALUES(25) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_26 VALUES(26) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_27 VALUES(27) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_28 VALUES(28) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_29 VALUES(29) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_30 VALUES(30) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_31 VALUES(31) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_32 VALUES(32) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_33 VALUES(33) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_34 VALUES(34) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_35 VALUES(35) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_36 VALUES(36) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_37 VALUES(37) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_38 VALUES(38) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_39 VALUES(39) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_40 VALUES(40) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_41 VALUES(41) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_42 VALUES(42) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_43 VALUES(43) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_44 VALUES(44) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_45 VALUES(45) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_46 VALUES(46) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_47 VALUES(47) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_48 VALUES(48) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_49 VALUES(49) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_50 VALUES(50) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_51 VALUES(51) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_52 VALUES(52) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_53 VALUES(53) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_54 VALUES(54) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_55 VALUES(55) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_56 VALUES(56) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_57 VALUES(57) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_58 VALUES(58) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_59 VALUES(59) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_60 VALUES(60) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_61 VALUES(61) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_62 VALUES(62) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_63 VALUES(63) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_64 VALUES(64) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_65 VALUES(65) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_66 VALUES(66) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_67 VALUES(67) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_68 VALUES(68) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_69 VALUES(69) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_70 VALUES(70) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_71 VALUES(71) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_72 VALUES(72) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_73 VALUES(73) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_74 VALUES(74) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_75 VALUES(75) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_76 VALUES(76) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_77 VALUES(77) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_78 VALUES(78) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_79 VALUES(79) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_80 VALUES(80) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_81 VALUES(81) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_82 VALUES(82) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_83 VALUES(83) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_84 VALUES(84) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_85 VALUES(85) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_86 VALUES(86) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_87 VALUES(87) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_88 VALUES(88) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_89 VALUES(89) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_90 VALUES(90) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_91 VALUES(91) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_92 VALUES(92) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_93 VALUES(93) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_94 VALUES(94) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_95 VALUES(95) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_96 VALUES(96) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_97 VALUES(97) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_98 VALUES(98) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_99 VALUES(99) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_100 VALUES(100) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_101 VALUES(101) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_102 VALUES(102) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_103 VALUES(103) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_104 VALUES(104) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_105 VALUES(105) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_106 VALUES(106) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_107 VALUES(107) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_108 VALUES(108) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_109 VALUES(109) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_110 VALUES(110) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_111 VALUES(111) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_112 VALUES(112) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_113 VALUES(113) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_114 VALUES(114) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_115 VALUES(115) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_116 VALUES(116) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_117 VALUES(117) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_118 VALUES(118) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_119 VALUES(119) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_120 VALUES(120) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_121 VALUES(121) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_122 VALUES(122) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_123 VALUES(123) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_124 VALUES(124) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_125 VALUES(125) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_126 VALUES(126) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_127 VALUES(127) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_128 VALUES(128) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_129 VALUES(129) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_130 VALUES(130) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_131 VALUES(131) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_132 VALUES(132) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_133 VALUES(133) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_134 VALUES(134) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_135 VALUES(135) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_136 VALUES(136) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_137 VALUES(137) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_138 VALUES(138) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_139 VALUES(139) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_140 VALUES(140) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_141 VALUES(141) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_142 VALUES(142) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_143 VALUES(143) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_144 VALUES(144) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_145 VALUES(145) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_146 VALUES(146) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_147 VALUES(147) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_148 VALUES(148) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_149 VALUES(149) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_crd_150 VALUES(150) STORAGE(ON "MAIN", CLUSTERBTR)
) STORAGE(ON "MAIN", CLUSTERBTR);

-- 4.增加注释
comment on table cost.pro_cost_cal_res_detail is '项目成本核算结果明细表';
comment on column cost.pro_cost_cal_res_detail.id is '主键id';
comment on column cost.pro_cost_cal_res_detail.cal_res_id is '项目成本核算主表id';
comment on column cost.pro_cost_cal_res_detail.check_scheme_id is '核算方案主表id';
comment on column cost.pro_cost_cal_res_detail.organization_id is '科室id';
comment on column cost.pro_cost_cal_res_detail.hospital_structure_id is '院区id';
comment on column cost.pro_cost_cal_res_detail.organization_cal_method is '核算科室采用核算方法id';
comment on column cost.pro_cost_cal_res_detail.serve_item_code is '服务项目编码';
comment on column cost.pro_cost_cal_res_detail.number is '服务项目数量';
comment on column cost.pro_cost_cal_res_detail.price is '服务项目单价';
comment on column cost.pro_cost_cal_res_detail.cost_item_code is '成本项目编码';
comment on column cost.pro_cost_cal_res_detail.direct_cost is '直接成本';
comment on column cost.pro_cost_cal_res_detail.indirect_cost_bk is '间接成本_本科间接';
comment on column cost.pro_cost_cal_res_detail.indirect_cost_gl is '间接成本_管理分摊';
comment on column cost.pro_cost_cal_res_detail.indirect_cost_yf is '间接成本_医辅分摊';
comment on column cost.pro_cost_cal_res_detail.medical_cost_type is '成本维度';
comment on column cost.pro_cost_cal_res_detail.create_user_code is '创建人编码';
comment on column cost.pro_cost_cal_res_detail.create_time is '创建时间';
comment on column cost.pro_cost_cal_res_detail.update_user_code is '修改人编码';
comment on column cost.pro_cost_cal_res_detail.update_time is '修改时间';
comment on column cost.pro_cost_cal_res_detail.delete_flag is '删除标记:0.存在,1.已删除';
comment on column cost.pro_cost_cal_res_detail.sink_cost is '单位沉没成本';
comment on column cost.pro_cost_cal_res_detail.cooperation_cost is '协作成本';
comment on column cost.pro_cost_cal_res_detail.transfer_sign is '转移标记:0.否,1.是';
comment on column cost.pro_cost_cal_res_detail.transfer_organization_id is '转移科室id';
comment on column cost.pro_cost_cal_res_detail.sink_hr is '沉没成本_人力';
comment on column cost.pro_cost_cal_res_detail.sink_dev is '沉没成本_资产';
comment on column cost.pro_cost_cal_res_detail.cost_sum is '单位成本';

-- 5.删除和重建索引
declare v_idx_sql nvarchar(5000); -- 定义sql语句
begin
	-- 删除索引idx_pro_cost_cal_res_detail
	set v_idx_sql = 'drop index if exists idx_pro_cost_cal_res_detail';
    execute immediate v_idx_sql;

    -- 创建索引pro_cost_cal_res_detail
    set v_idx_sql = 'create index idx_pro_cost_cal_res_detail on cost.pro_cost_cal_res_detail(id)';
    execute immediate v_idx_sql;
end;

-- 6.项目成本核算结果明细数据迁移
insert into cost.pro_cost_cal_res_detail(
    id, cal_res_id, check_scheme_id, organization_id,
    hospital_structure_id, organization_cal_method, serve_item_code, number,
    price, cost_item_code, direct_cost, indirect_cost_bk,
    indirect_cost_gl, indirect_cost_yf, medical_cost_type, create_user_code,
    create_time, update_user_code, update_time, delete_flag,
    sink_cost, cooperation_cost, transfer_sign, transfer_organization_id,
    sink_hr, sink_dev, cost_sum
)
select id, cal_res_id, check_scheme_id, organization_id,
       hospital_structure_id, organization_cal_method, serve_item_code, number,
       price, cost_item_code, direct_cost, indirect_cost_bk,
       indirect_cost_gl, indirect_cost_yf, medical_cost_type, create_user_code,
       create_time, update_user_code, update_time, delete_flag,
       sink_cost, cooperation_cost, transfer_sign, transfer_organization_id,
       sink_hr, sink_dev, cost_sum
from cost.pro_cost_cal_res_detail_old;
commit;

-- 7.删除原核算结果明细表
drop table if exists cost.pro_cost_cal_res_detail_old;

-- 8.查看表分区是否存在
select count(1)
        from user_tab_partitions
        where lower(table_name) = 'pro_cost_cal_res_detail'
          and lower(partition_name) = 'p_crd_100';

select count(1) from user_tab_partitions where lower(table_name) = 'pro_cost_cal_res_detail' and lower(partition_name) = 'p_crd_86'；

-- 9.增加分区
ALTER TABLE cost.pro_cost_cal_res_detail ADD PARTITION p_crd_100 VALUES(100);

alter table cost.pro_cost_cal_res_detail add partition p_crd_86 values(86)

-- 10.删除分区
    使用 DROP PARTITION 可将分区的定义连同数据一起删除，示例语句如下：
    ALTER TABLE cost.pro_cost_cal_res_detail DROP PARTITION p_crd_100;
    使用 TRUNCATE PARTITION 仅删除分区的数据，示例语句如下：
    alter table cost.pro_cost_cal_res_detail truncate partition p_crd_100;
	
-- 11.指定分区写入数据
insert into
	cost.pro_cost_cal_res_detail partition(p_crd_86)( cal_res_id,
	check_scheme_id,
	hospital_structure_id,
	organization_id,
	organization_cal_method,
	serve_item_code,
	number,
	price,
	cost_item_code,
	direct_cost,
	indirect_cost_bk,
	indirect_cost_gl,
	indirect_cost_yf,
	medical_cost_type,
	cooperation_cost,
	transfer_sign,
	transfer_organization_id,
	sink_hr,
	sink_dev,
	cost_sum,
	sink_cost,
	delete_flag )
select
	tt1.calculate_result_id,
	tt1.check_scheme_id,
	tt1.hospital_structure_id,
	tt1.organization_id,
	tt1.organization_calculate_method_id,
	tt1.serve_item_code,
	tt1.number,
	tt1.price,
	tt1.cost_item_code,
	tt1.direct_cost,
	tt1.indirect_cost_self_organization,
	tt1.indirect_cost_manage_share,
	tt1.indirect_cost_medical_assistance_share,
	tt1.medical_cost_type,
	tt1.cooperation_cost,
	tt1.transfer_sign,
	tt1.transfer_organization_id,
	tt1.sink_hr,
	tt1.sink_dev,
	tt1.cost_sum,
	(tt1.sink_hr + tt1.sink_dev),
	0 delete_flag
from
	(
	select
		row_number() over(
		order by t1.cal_res_id asc) as rownumber,
		t1.cal_res_id calculate_result_id,
		t1.check_scheme_id,
		t1.hospital_structure_id,
		t1.organization_id,
		ccr.organization_cal_method organization_calculate_method_id,
		ccr.serve_item_code,
		ccr.number,
		ccr.price,
		t1.cost_item_code,
		sum(t1.direct_cost) direct_cost,
		sum(t1.indirect_cost_bk) indirect_cost_self_organization,
		sum(t1.indirect_cost_gl) indirect_cost_manage_share,
		sum(t1.indirect_cost_yf) indirect_cost_medical_assistance_share,
		sum(t1.cooperation_cost) cooperation_cost,
		ccr.medical_cost_type,
		ccr.transfer_sign,
		ccr.transfer_organization_id,
		ccr.sink_hr,
		ccr.sink_dev,
		sum(t1.direct_cost + t1.indirect_cost_bk + t1.indirect_cost_gl + t1.indirect_cost_yf) cost_sum
	from
		(
		select
			ddh.cal_res_id,
			ddh.check_scheme_id,
			ddh.hospital_structure_id,
			ddh.organization_id,
			ddh.cost_item_code,
			isnull(ddh.hr_work_cal_cost, 0) direct_cost,
			0 indirect_cost_bk,
			0 indirect_cost_gl,
			0 indirect_cost_yf,
			isnull(ddh.cooperation_cost, 0) cooperation_cost
		from
			cost.pro_cost_cal_detail_direct_hr ddh
		where
			ddh.check_scheme_id = 86
			and ddh.organization_id = 13
	union all
		select
			ddd.cal_res_id,
			ddd.check_scheme_id,
			ddd.hospital_structure_id,
			ddd.organization_id,
			ddd.cost_item_code,
			isnull(ddd.dev_work_cal_cost, 0) direct_cost,
			0 indirect_cost_bk,
			0 indirect_cost_gl,
			0 indirect_cost_yf,
			0 cooperation_cost
		from
			cost.pro_cost_cal_detail_direct_dev ddd
		where
			ddd.check_scheme_id = 86
			and ddd.organization_id = 13
	union all
		select
			ddm.cal_res_id,
			ddm.check_scheme_id,
			ddm.hospital_structure_id,
			ddm.organization_id,
			ddm.cost_item_code,
			isnull(ddm.mate_cal_cost, 0) direct_cost,
			0 indirect_cost_bk,
			0 indirect_cost_gl,
			0 indirect_cost_yf,
			0 cooperation_cost
		from
			cost.pro_cost_cal_detail_direct_mate ddm
		where
			ddm.check_scheme_id = 86
			and ddm.organization_id = 13
	union all
		select
			ddo.cal_res_id,
			ddo.check_scheme_id,
			ddo.hospital_structure_id,
			ddo.organization_id,
			ddo.cost_item_code,
			isnull(ddo.cost_value, 0) direct_cost,
			0 indirect_cost_bk,
			0 indirect_cost_gl,
			0 indirect_cost_yf,
			0 cooperation_cost
		from
			cost.pro_cost_cal_detail_direct_other ddo
		where
			ddo.check_scheme_id = 86
			and ddo.organization_id = 13
	union all
		select
			csi.cal_res_id,
			csi.check_scheme_id,
			csi.hospital_structure_id,
			csi.organization_id,
			csi.cost_item_code,
			0 direct_cost,
			sum(isnull(csi.indirect_cost_bk, 0)) indirect_cost_bk,
			sum(isnull(csi.indirect_cost_gl, 0)) indirect_cost_gl,
			sum(isnull(csi.indirect_cost_yf, 0)) indirect_cost_yf,
			0 cooperation_cost
		from
			cost.pro_cost_cal_serve_indirect csi
		where
			csi.check_scheme_id = 86
			and csi.organization_id = 13
		group by
			csi.cal_res_id,
			csi.check_scheme_id,
			csi.hospital_structure_id,
			csi.organization_id,
			csi.cost_item_code ) t1
	join cost.pro_cost_cal_res ccr on
		ccr.check_scheme_id = 86
		and ccr.id = t1.cal_res_id
		and ccr.organization_id = 13
	group by
		t1.cal_res_id,
		t1.check_scheme_id,
		t1.hospital_structure_id,
		t1.organization_id,
		ccr.organization_cal_method,
		ccr.serve_item_code,
		ccr.number,
		ccr.price,
		t1.cost_item_code,
		ccr.medical_cost_type,
		ccr.transfer_sign,
		ccr.transfer_organization_id,
		ccr.sink_hr,
		ccr.sink_dev ) tt1